<?php
/**
* @file $Id: Attendance.php 437 2007-04-23 00:57:51Z focus-sis $
* @package Focus/SIS
* @copyright Copyright (C) 2006 Andrew Schmadeke. All rights reserved.  
* changes Copyright (C) 2007 Brent Larsen. All rights reserved.
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL, see LICENSE.txt
* Focus/SIS is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
* other free or open source software licenses.
* See COPYRIGHT.txt for copyright notices and details.
*/

function UpdateAttendanceDaily($student_id, $date = '', $comment = false) {
	$user_syear = UserSyear();

	if (!$date) {
		$date = DBDate();
	}
	
	$time = mktime(0, 0, 0, (MonthNWSwitch(substr($date, 3, 3)) * 1)
			, (substr($date, 0, 2) * 1), substr($date, 7));
			
	$day = DayNameToLetter(date('D',$time));

	if (SystemPreferences('VARIABLE_PERIOD_LENGTH') == 'Y') {
		$length_col = 'LENGTH_'.$day;
	}	
	else {
		$length_col = 'LENGTH';
	}

	$applicable_mps = GetAllMP('QTR', GetCurrentMP('QTR', $date));
	$RET = DBGet(DBQuery("
			SELECT       sum(sp.$length_col) AS TOTAL
			FROM         SCHEDULE s
			 ,           COURSE_PERIODS cp
			 ,           SCHOOL_PERIODS sp
			 ,           ATTENDANCE_CALENDAR ac
			WHERE        s.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID 
			 AND         cp.DOES_ATTENDANCE = 'Y'
			 AND         ac.SCHOOL_DATE = '$date' 
			 AND (       ac.BLOCK = sp.BLOCK 
			  OR         sp.BLOCK IS NULL )
			 AND         ac.CALENDAR_ID = cp.CALENDAR_ID 
			 AND         ac.SCHOOL_ID = s.SCHOOL_ID 
			 AND         ac.SYEAR = s.SYEAR
			 AND         s.SYEAR = cp.SYEAR 
			 AND         sp.PERIOD_ID = cp.PERIOD_ID
			 AND         cp.DAYS LIKE '%$day%'
			 AND         s.STUDENT_ID = '$student_id' 
			 AND         s.SYEAR = '$user_syear' 
			 AND (       '$date' BETWEEN s.START_DATE AND s.END_DATE 
			  OR   (     s.END_DATE IS NULL 
			   AND       '$date' >= s.START_DATE ) )
			 AND         s.MARKING_PERIOD_ID IN ($applicable_mps)
			"));			

	$total = $RET[1]['TOTAL'];
	
	if ($total == 0) {
		return;
	}
	
	$RET = DBGet(DBQuery("
			SELECT       sum(sp.$length_col) AS TOTAL
			FROM         ATTENDANCE_PERIOD ap
			 ,           SCHOOL_PERIODS sp
			 ,           ATTENDANCE_CODES ac
			WHERE        ap.STUDENT_ID = '$student_id' 
			 AND         ap.SCHOOL_DATE = '$date' 
			 AND         ap.PERIOD_ID = sp.PERIOD_ID 
			 AND         ac.ID = ap.ATTENDANCE_CODE 
			 AND         ac.STATE_CODE = 'A'
			 AND         sp.SYEAR = '$user_syear'
			"));
			
	$total -= $RET[1]['TOTAL'];

	$RET = DBGet(DBQuery("
			SELECT       sum(sp.$length_col) AS TOTAL
			FROM         ATTENDANCE_PERIOD ap
			 ,           SCHOOL_PERIODS sp
			 ,           ATTENDANCE_CODES ac
			WHERE        ap.STUDENT_ID = '$student_id' 
			 AND         ap.SCHOOL_DATE = '$date' 
			 AND         ap.PERIOD_ID = sp.PERIOD_ID 
			 AND         ac.ID = ap.ATTENDANCE_CODE 
			 AND         ac.STATE_CODE = 'H'
			 AND         sp.SYEAR = '$user_syear'
			"));
			
	$total -= $RET[1]['TOTAL'] * .5;
	
	if ($total >= 300) {   //this looks like a good candidate for a function
		$length = '1.0';
	}	
	elseif ($total >= 150) {
		$length = '.5';
	}	
	else {
		$length = '0.0';
	}	
	
	$current_RET = DBGet(DBQuery("
			SELECT      MINUTES_PRESENT
			 ,          STATE_VALUE
			 ,          COMMENT 
			FROM        ATTENDANCE_DAY 
			WHERE       STUDENT_ID = '$student_id' 
			 AND        SCHOOL_DATE = '$date'
			"));
			
	if (count($current_RET) 
	&& $current_RET[1]['MINUTES_PRESENT'] != $total
	) {
		DBQuery("
				UPDATE       ATTENDANCE_DAY 
				SET          MINUTES_PRESENT = '$total'
				 ,           STATE_VALUE = '$length'"
				.($comment !== false ? "
				 ,           COMMENT = '".str_replace("\'", "''", $comment)."'"	: '')." 
				WHERE        STUDENT_ID = '$student_id' 
				 AND         SCHOOL_DATE = '$date'
				");
	}
	elseif (count($current_RET) 
	&& $comment !== false 
	&& $current_RET[1]['COMMENT'] != $comment
	) {
		DBQuery("
				UPDATE       ATTENDANCE_DAY 
				SET          COMMENT = '".str_replace("\'", "''", $comment)."' 
				WHERE        STUDENT_ID = '$student_id' 
				 AND         SCHOOL_DATE = '$date'
				");
	}
	elseif (count($current_RET) == 0) {
		DBQuery("
				INSERT INTO  ATTENDANCE_DAY 
				(  SYEAR
				 , STUDENT_ID
				 , SCHOOL_DATE
				 , MINUTES_PRESENT
				 , STATE_VALUE
				 , MARKING_PERIOD_ID
				 , COMMENT
				) values
				(  '$user_syear'
				 , '$student_id'
				 , '$date'
				 , '$total'
				 , '$length'
				 , '".GetCurrentMP('QTR',$date)."'
				 , '".str_replace("\'","''",$comment)."'
				)
				");
	}
}

function DayNameToLetter($day) {
	switch($day) {
		case 'Sun': {
			return 'U';
		} break;
		
		case 'Thu': {
			return 'H';
		} break;
		
		default: {
			return substr($day, 0, 1);	
		} break;
	}	
}

function CalcGPA($student_id, $marking_period_id) {	
	$mps_RET = DBGet(DBQuery("
			SELECT       MARKING_PERIOD_ID 
			FROM         ".GetMP($_REQUEST['marking_period_id'], 'TABLE')
			));
	$mps = '';
	
	foreach ($mps_RET as $mp) {
		$mps .= ",'".$mp['MARKING_PERIOD_ID']."'";
	}
	$mps = '('.substr($mps,1).')';
	
	$gpa_RET = DBGet(DBQuery("
			SELECT       COALESCE(sum(g.GPA_POINTS) / sum(g.CREDITS), 0) AS GPA
			 ,           COALESCE(sum(g.WEIGHTED_GPA_POINTS) / sum(g.CREDITS), 0) 
							AS WEIGHTED_GPA 
			FROM         STUDENT_REPORT_CARD_GRADES g 
			WHERE        g.CREDITS > 0 
			 AND         g.AFFECTS_GPA = 'Y' 
			 AND         g.MARKING_PERIOD_ID IN $mps
			 AND         g.STUDENT_ID = '$student_id'
			"));

	DBQuery("
			DELETE FROM  STUDENT_GPA_CALCULATED 
			WHERE        STUDENT_ID = '$student_id' 
			 AND         MARKING_PERIOD_ID = '$marking_period_id'
			");
	
	DBQuery("
			INSERT INTO  STUDENT_GPA_CALCULATED 
			(  student_id
			 , marking_period_id
			 , gpa
			 , weighted_gpa
			 , cumulative_gpa
			 , cumulative_weighted_gpa
			) 
			SELECT       '$student_id'
			 ,           ".db_to_number('g.marking_period_id')." AS MARKING_PERIOD_ID
			 ,           sum(g.GPA_POINTS)/sum(g.CREDITS) AS GPA
			 ,           sum(g.WEIGHTED_GPA_POINTS)/sum(g.CREDITS) AS WEIGHTED_GPA
			 ,           '".$gpa_RET[1]['GPA']."' AS CUMULATIVE_GPA
			 ,           '".$gpa_RET[1]['WEIGHTED_GPA']."' CUMULATIVE_WEIGHTED_GPA 
			FROM         student_report_card_grades g 
			WHERE        g.STUDENT_ID = '$student_id' 
			 AND         g.CREDITS > 0 
			 AND         g.AFFECTS_GPA = 'Y' 
			 AND         g.marking_period_id = '".$_REQUEST['marking_period_id']."' 
			GROUP BY     g.marking_period_id 
			 HAVING      sum(g.CREDITS) > 0
			");
}
?>